What is the difference between DATE, DATETIME, and TIMESTAMP?

Question

Anubhav Kumar · Answer

Note:
DATE → Only the date.DATETIME → Date + Time (no timezone conversion).TIMESTAMP → Date + Time with timezone/UTC awareness (in MySQL), or row versioning (in SQL Server).
DATE
Stores only the calendar date (no time).Format: YYYY-MM-DD.Storage size: 3 bytes.Range: 1000-01-01 to 9999-12-31.
CREATE TABLE ExSampleDate (dob DATE);
INSERT INTO ExSampleDate VALUES ('2025-08-19');
SELECT dob FROM ExSampleDate ;
DATETIME
Stores date + time (no timezone conversion).Format: YYYY-MM-DD hh:mm:ss.Storage size: 8 bytes.Range: 1753-01-01 00:00:00 to 9999-12-31 23:59:59.
CREATE TABLE EXSampleDateTime (created_at DATETIME);
INSERT INTO EXSampleDateTime VALUES ('2025-08-19 14:30:45');
SELECT created_at FROM EXSampleDateTime ;
TIMESTAMP (behavior differs by DBMS)
In MySQL:
Stores date + time similar to DATETIME but in 
UTC internally.Automatically updates to current time if configured with DEFAULT CURRENT_TIMESTAMP.Format: YYYY-MM-DD hh:mm:ss.Storage size: 4 bytes.Range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.
In SQL Server / Oracle, TIMESTAMP usually means a
row-versioning / sequence number (not date-time).
Example (MySQL)
CREATE TABLE EXSampleTimestamp (
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO EXSampleTimestamp VALUES (DEFAULT);
SELECT updated_at FROM EXSampleTimestamp ;

forum

What is the difference between DATE, DATETIME, and TIMESTAMP?

ICSM

Can you answer this question?

1 Answers

Note:

DATE

DATETIME

TIMESTAMP (behavior differs by DBMS)

Liked By